using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using BlazorSodDbManager.Models;
using PWMIS.DataMap.Entity;
using PWMIS.Core.Extensions;
using PWMIS.DataProvider.Data;
using PWMIS.DataProvider.Adapter;

namespace BlazorSodDbManager.Services
{
    public class DatabaseService : IDatabaseService
    {
        private AdoHelper GetDbHelper(DatabaseConnection connection)
        {
            AdoHelper helper = null;
            switch (connection.Type)
            {
                case DatabaseType.SqlServer:
                    helper = new SqlServer();
                    helper.ConnectionString = connection.GetConnectionString();
                    return helper;
                case DatabaseType.MySQL:
                    return AdoHelper.CreateHelper("MySql", connection.GetConnectionString());
                case DatabaseType.Oracle:
                    return AdoHelper.CreateHelper("Oracle", connection.GetConnectionString());
                case DatabaseType.SQLite:
                    return AdoHelper.CreateHelper("SQLite", connection.GetConnectionString());
                case DatabaseType.Access:
                    return AdoHelper.CreateHelper("Access", connection.GetConnectionString());
                case DatabaseType.PostgreSQL:
                    helper = new PostgreSQL();
                    helper.ConnectionString = connection.GetConnectionString();
                    return helper;
                default:
                    throw new NotSupportedException($"Database type {connection.Type} is not supported.");
            }
        }

        public async Task<bool> TestConnectionAsync(DatabaseConnection connection)
        {
            try
            {
                var dbHelper = GetDbHelper(connection);
                using (var conn = dbHelper.GetConnection(connection.GetConnectionString()))
                {
                    await Task.Run(() => conn.Open());
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public async Task<List<string>> GetDatabasesAsync(DatabaseConnection connection)
        {
            var result = new List<string>();
            var dbHelper = GetDbHelper(connection);

            try
            {
                string query = string.Empty;

                switch (connection.Type)
                {
                    case DatabaseType.SqlServer:
                        query = "SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') ORDER BY name";
                        break;
                    case DatabaseType.MySQL:
                        query = "SHOW DATABASES";
                        break;
                    case DatabaseType.Oracle:
                        query = "SELECT USERNAME FROM ALL_USERS ORDER BY USERNAME";
                        break;
                    case DatabaseType.SQLite:
                        // SQLite has only one database per file
                        result.Add(connection.Database);
                        return result;
                    case DatabaseType.Access:
                        // Access has only one database per file
                        result.Add(connection.Database);
                        return result;
                    case DatabaseType.PostgreSQL:
                        query = "SELECT datname FROM pg_database WHERE datistemplate = false ORDER BY datname";
                        break;
                }

                var dt = await Task.Run(() => dbHelper.ExecuteDataSet(query));
                if (dt != null && dt.Tables.Count > 0)
                    foreach (DataRow row in dt.Tables[0].Rows)
                    {
                        result.Add(row[0].ToString() ?? string.Empty);
                    }

                return result;
            }
            catch (Exception ex)
            {
                throw new Exception($"Error getting databases: {ex.Message}", ex);
            }
        }

        public async Task<List<TableInfo>> GetTablesAsync(DatabaseConnection connection)
        {
            var result = new List<TableInfo>();
            var dbHelper = GetDbHelper(connection);

            try
            {
                string query = string.Empty;

                switch (connection.Type)
                {
                    case DatabaseType.SqlServer:
                        query = @"
                            SELECT 
                                t.TABLE_SCHEMA as [Schema], 
                                t.TABLE_NAME as [Name],
                                t.TABLE_TYPE as [Type]
                            FROM 
                                INFORMATION_SCHEMA.TABLES t
                            ORDER BY 
                                t.TABLE_SCHEMA, t.TABLE_NAME";
                        break;
                    case DatabaseType.MySQL:
                        query = @"
                            SELECT 
                                TABLE_SCHEMA as `Schema`, 
                                TABLE_NAME as `Name`,
                                TABLE_TYPE as `Type`
                            FROM 
                                INFORMATION_SCHEMA.TABLES 
                            WHERE 
                                TABLE_SCHEMA = DATABASE()
                            ORDER BY 
                                TABLE_NAME";
                        break;
                    case DatabaseType.Oracle:
                        query = @"
                            SELECT 
                                OWNER as ""Schema"", 
                                TABLE_NAME as ""Name"",
                                'TABLE' as ""Type""
                            FROM 
                                ALL_TABLES
                            WHERE 
                                OWNER = USER
                            ORDER BY 
                                TABLE_NAME";
                        break;
                    case DatabaseType.SQLite:
                        query = @"
                            SELECT 
                                '' as [Schema], 
                                name as [Name],
                                'TABLE' as [Type]
                            FROM 
                                sqlite_master
                            WHERE 
                                type='table' AND 
                                name NOT LIKE 'sqlite_%'
                            ORDER BY 
                                name";
                        break;
                    case DatabaseType.Access:
                        query = @"
                            SELECT 
                                '' as [Schema], 
                                Name as [Name],
                                'TABLE' as [Type]
                            FROM 
                                MSysObjects
                            WHERE 
                                Type=1 AND 
                                Flags=0
                            ORDER BY 
                                Name";
                        break;
                    case DatabaseType.PostgreSQL:
                        query = @"
                            SELECT 
                                schemaname as ""Schema"",
                                tablename as ""Name"",
                                'TABLE' as ""Type""
                            FROM 
                                pg_catalog.pg_tables
                            WHERE 
                                schemaname NOT IN ('pg_catalog', 'information_schema')
                            ORDER BY 
                                schemaname, tablename";
                        break;
                }

                var dt = await Task.Run(() => dbHelper.ExecuteDataSet(query));

                foreach (DataRow row in dt.Tables[0].Rows)
                {
                    result.Add(new TableInfo
                    {
                        Schema = row["Schema"].ToString(),
                        TableName = row["Name"].ToString() ?? string.Empty,
                        TableType = row["Type"].ToString() ?? string.Empty
                    });
                }

                return result;
            }
            catch (Exception ex)
            {
                throw new Exception($"Error getting tables: {ex.Message}", ex);
            }
        }

        public async Task<TableInfo> GetTableInfoAsync(DatabaseConnection connection, string tableName, string? schema = null)
        {
            var dbHelper = GetDbHelper(connection);
            var tableInfo = new TableInfo
            {
                TableName = tableName,
                Schema = schema
            };

            try
            {
                // Get columns
                string columnsQuery = string.Empty;

                switch (connection.Type)
                {
                    case DatabaseType.SqlServer:
                        columnsQuery = @"
                            SELECT 
                                c.COLUMN_NAME,
                                c.DATA_TYPE,
                                c.IS_NULLABLE,
                                COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') as IS_IDENTITY,
                                CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END AS IS_PRIMARY_KEY,
                                c.COLUMN_DEFAULT,
                                c.CHARACTER_MAXIMUM_LENGTH,
                                c.NUMERIC_PRECISION,
                                c.NUMERIC_SCALE,
                                c.ORDINAL_POSITION
                            FROM 
                                INFORMATION_SCHEMA.COLUMNS c
                            LEFT JOIN (
                                SELECT 
                                    ku.TABLE_CATALOG,
                                    ku.TABLE_SCHEMA,
                                    ku.TABLE_NAME,
                                    ku.COLUMN_NAME
                                FROM 
                                    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
                                JOIN 
                                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku
                                    ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
                                    AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME
                            ) pk ON 
                                c.TABLE_CATALOG = pk.TABLE_CATALOG
                                AND c.TABLE_SCHEMA = pk.TABLE_SCHEMA
                                AND c.TABLE_NAME = pk.TABLE_NAME
                                AND c.COLUMN_NAME = pk.COLUMN_NAME
                            WHERE 
                                c.TABLE_NAME = @tableName
                                AND c.TABLE_SCHEMA = @schema
                            ORDER BY 
                                c.ORDINAL_POSITION";
                        break;
                    case DatabaseType.PostgreSQL:
                        columnsQuery = @"
                            SELECT 
                                a.attname AS COLUMN_NAME,
                                pg_catalog.format_type(a.atttypid, a.atttypmod) AS DATA_TYPE,
                                CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END AS IS_NULLABLE,
                                CASE 
                                    WHEN a.attidentity = 'a' OR a.attidentity = 'd' THEN 1
                                    WHEN EXISTS (
                                        SELECT 1 FROM pg_catalog.pg_attrdef d
                                        WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum
                                        AND d.adsrc LIKE 'nextval%'
                                    ) THEN 1
                                    ELSE 0
                                END AS IS_IDENTITY,
                                CASE WHEN pk.attnum IS NOT NULL THEN 1 ELSE 0 END AS IS_PRIMARY_KEY,
                                pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) AS COLUMN_DEFAULT,
                                CASE 
                                    WHEN a.atttypid IN (1042, 1043) THEN a.atttypmod - 4
                                    ELSE 0
                                END AS CHARACTER_MAXIMUM_LENGTH,
                                CASE 
                                    WHEN a.atttypid IN (21, 23, 20, 700, 701) THEN pg_catalog.format_type(a.atttypid, a.atttypmod)::text
                                    ELSE NULL
                                END AS NUMERIC_PRECISION,
                                CASE 
                                    WHEN a.atttypid IN (1700) THEN (((a.atttypmod - 4) >> 16) & 65535)
                                    WHEN a.atttypid IN (700, 701) THEN NULL
                                    ELSE 0
                                END AS NUMERIC_SCALE,
                                a.attnum AS ORDINAL_POSITION
                            FROM 
                                pg_catalog.pg_attribute a
                            LEFT JOIN pg_catalog.pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
                            LEFT JOIN (
                                SELECT ta.attnum, ta.attrelid
                                FROM pg_catalog.pg_attribute ta
                                JOIN pg_catalog.pg_index i ON ta.attrelid = i.indrelid AND ta.attnum = ANY(i.indkey)
                                WHERE i.indisprimary
                            ) pk ON a.attrelid = pk.attrelid AND a.attnum = pk.attnum
                            JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
                            JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
                            WHERE 
                                a.attnum > 0 
                                AND NOT a.attisdropped
                                AND c.relname = @tableName
                                AND n.nspname = @schema
                            ORDER BY 
                                a.attnum";
                        break;
                    // Add other database types here...
                    default:
                        columnsQuery = ""; // Simplified for brevity
                        break;
                }

                if (!string.IsNullOrEmpty(columnsQuery))
                {
                    List<IDataParameter> parameters = new List<IDataParameter>();
                    parameters.Add(dbHelper.GetParameter("@tableName", tableName));
                    if (!string.IsNullOrEmpty(schema))
                    {
                        parameters.Add(dbHelper.GetParameter("@schema", schema));
                    }
                    else if (connection.Type == DatabaseType.SqlServer)
                    {
                        parameters.Add(dbHelper.GetParameter("@schema", "dbo"));
                    }
                    else if (connection.Type == DatabaseType.PostgreSQL)
                    {
                        parameters.Add(dbHelper.GetParameter("@schema", "public"));
                    }
                    var dt = dbHelper.GetSchema("Columns", new string[] { null,null,tableName });
                    //var dt = await Task.Run(() => dbHelper.ExecuteDataSet(connection.GetConnectionString(), CommandType.Text, columnsQuery, parameters.ToArray()));

                    foreach (DataRow row in dt.Rows)
                    {
                        tableInfo.Columns.Add(new ColumnInfo
                        {
                            ColumnName = row["COLUMN_NAME"].ToString() ?? string.Empty,
                            DataType = row["DATA_TYPE"].ToString() ?? string.Empty,
                            IsNullable = row["IS_NULLABLE"].ToString() == "YES",
                            //IsPrimaryKey = Convert.ToBoolean(row["IS_PRIMARY_KEY"]),
                            //IsIdentity = Convert.ToBoolean(row["IS_IDENTITY"]),
                            DefaultValue = row["COLUMN_DEFAULT"]?.ToString(),
                            MaxLength = row["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]) : 0,
                            Precision = row["NUMERIC_PRECISION"] != DBNull.Value ? Convert.ToInt32(row["NUMERIC_PRECISION"]) : 0,
                            Scale = row["NUMERIC_SCALE"] != DBNull.Value ? Convert.ToInt32(row["NUMERIC_SCALE"]) : 0,
                            OrdinalPosition = Convert.ToInt32(row["ORDINAL_POSITION"])
                        });
                    }
                }

                return tableInfo;
            }
            catch (Exception ex)
            {
                throw new Exception($"Error getting table info: {ex.Message}", ex);
            }
        }

        public async Task<QueryResult> ExecuteQueryAsync(DatabaseConnection connection, string query)
        {
            var result = new QueryResult
            {
                Query = query,
                IsSuccess = false
            };

            var dbHelper = GetDbHelper(connection);
            var stopwatch = new Stopwatch();

            try
            {
                stopwatch.Start();
                var dt = await Task.Run(() => dbHelper.ExecuteDataSet(query));
                stopwatch.Stop();

                result.ResultSets.Add(dt.Tables[0]);
                result.ExecutionTime = stopwatch.Elapsed;
                result.IsSuccess = true;

                return result;
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                result.ErrorMessage = ex.Message;
                result.ExecutionTime = stopwatch.Elapsed;
                return result;
            }
        }

        public async Task<QueryResult> ExecuteNonQueryAsync(DatabaseConnection connection, string query)
        {
            var result = new QueryResult
            {
                Query = query,
                IsSuccess = false
            };

            var dbHelper = GetDbHelper(connection);
            var stopwatch = new Stopwatch();

            try
            {
                stopwatch.Start();
                var rowsAffected = await Task.Run(() => dbHelper.ExecuteNonQuery(query));
                stopwatch.Stop();

                result.RowsAffected = rowsAffected;
                result.ExecutionTime = stopwatch.Elapsed;
                result.IsSuccess = true;

                return result;
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                result.ErrorMessage = ex.Message;
                result.ExecutionTime = stopwatch.Elapsed;
                return result;
            }
        }

        public async Task<List<string>> GetStoredProceduresAsync(DatabaseConnection connection)
        {
            var result = new List<string>();
            var dbHelper = GetDbHelper(connection);

            try
            {
                string query = string.Empty;

                switch (connection.Type)
                {
                    case DatabaseType.SqlServer:
                        query = "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' ORDER BY ROUTINE_NAME";
                        break;
                    case DatabaseType.MySQL:
                        query = "SHOW PROCEDURE STATUS WHERE Db = DATABASE()";
                        break;
                    case DatabaseType.Oracle:
                        query = "SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE' AND OWNER = USER ORDER BY OBJECT_NAME";
                        break;
                    case DatabaseType.PostgreSQL:
                        query = @"
                            SELECT p.proname as procedure_name
                            FROM pg_catalog.pg_proc p
                            JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
                            WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
                            AND p.prokind = 'p'
                            ORDER BY procedure_name";
                        break;
                    default:
                        return result; // Some databases don't support stored procedures
                }

                var dt = await Task.Run(() => dbHelper.ExecuteDataSet(query));

                foreach (DataRow row in dt.Tables[0].Rows)
                {
                    result.Add(row[0].ToString() ?? string.Empty);
                }

                return result;
            }
            catch
            {
                return result;
            }
        }

        public async Task<List<string>> GetViewsAsync(DatabaseConnection connection)
        {
            var result = new List<string>();
            var dbHelper = GetDbHelper(connection);

            try
            {
                string query = string.Empty;

                switch (connection.Type)
                {
                    case DatabaseType.SqlServer:
                        query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS ORDER BY TABLE_NAME";
                        break;
                    case DatabaseType.MySQL:
                        query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = DATABASE() ORDER BY TABLE_NAME";
                        break;
                    case DatabaseType.Oracle:
                        query = "SELECT VIEW_NAME FROM ALL_VIEWS WHERE OWNER = USER ORDER BY VIEW_NAME";
                        break;
                    case DatabaseType.SQLite:
                        query = "SELECT name FROM sqlite_master WHERE type='view' ORDER BY name";
                        break;
                    case DatabaseType.PostgreSQL:
                        query = @"
                            SELECT table_name
                            FROM information_schema.views
                            WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
                            ORDER BY table_name";
                        break;
                    default:
                        return result;
                }

                var dt = await Task.Run(() => dbHelper.ExecuteDataSet(query));

                foreach (DataRow row in dt.Tables[0].Rows)
                {
                    result.Add(row[0].ToString() ?? string.Empty);
                }

                return result;
            }
            catch
            {
                return result;
            }
        }

        public async Task<List<string>> GetFunctionsAsync(DatabaseConnection connection)
        {
            var result = new List<string>();
            var dbHelper = GetDbHelper(connection);

            try
            {
                string query = string.Empty;

                switch (connection.Type)
                {
                    case DatabaseType.SqlServer:
                        query = "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' ORDER BY ROUTINE_NAME";
                        break;
                    case DatabaseType.MySQL:
                        query = "SHOW FUNCTION STATUS WHERE Db = DATABASE()";
                        break;
                    case DatabaseType.Oracle:
                        query = "SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION' AND OWNER = USER ORDER BY OBJECT_NAME";
                        break;
                    case DatabaseType.PostgreSQL:
                        query = @"
                            SELECT p.proname as function_name
                            FROM pg_catalog.pg_proc p
                            JOIN pg_catalog.pg_namespace n ON p.pronamespace = n.oid
                            WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
                            AND p.prokind = 'f'
                            ORDER BY function_name";
                        break;
                    default:
                        return result;
                }

                var dt = await Task.Run(() => dbHelper.ExecuteDataSet(query));

                foreach (DataRow row in dt.Tables[0].Rows)
                {
                    result.Add(row[0].ToString() ?? string.Empty);
                }

                return result;
            }
            catch
            {
                return result;
            }
        }

        public async Task<string> GenerateCreateTableScriptAsync(DatabaseConnection connection, string tableName, string? schema = null)
        {
            try
            {
                var tableInfo = await GetTableInfoAsync(connection, tableName, schema);
                var script = new System.Text.StringBuilder();

                // PostgreSQL的建表语法稍有不同
                if (connection.Type == DatabaseType.PostgreSQL)
                {
                    script.AppendLine($"CREATE TABLE {(string.IsNullOrEmpty(schema) ? "" : $"\"{schema}\".")}\"{tableName}\" (");

                    for (int i = 0; i < tableInfo.Columns.Count; i++)
                    {
                        var column = tableInfo.Columns[i];
                        script.Append($"    \"{column.ColumnName}\" {column.DataType}");

                        if (column.MaxLength > 0 && column.DataType.Contains("char"))
                        {
                            script.Append($"({column.MaxLength})");
                        }

                        if (column.IsIdentity)
                        {
                            // PostgreSQL中使用SERIAL或BIGSERIAL
                            if (column.DataType.Contains("int"))
                            {
                                // 如果已经是serial类型，则不添加
                                if (!column.DataType.Contains("serial"))
                                {
                                    script.Append(" GENERATED ALWAYS AS IDENTITY");
                                }
                            }
                        }

                        if (!column.IsNullable)
                        {
                            script.Append(" NOT NULL");
                        }

                        if (!string.IsNullOrEmpty(column.DefaultValue))
                        {
                            script.Append($" DEFAULT {column.DefaultValue}");
                        }

                        if (i < tableInfo.Columns.Count - 1)
                        {
                            script.AppendLine(",");
                        }
                        else
                        {
                            script.AppendLine();
                        }
                    }

                    // Add primary key constraint
                    var primaryKeyColumns = tableInfo.Columns.Where(c => c.IsPrimaryKey).ToList();
                    if (primaryKeyColumns.Any())
                    {
                        script.Append($"    CONSTRAINT \"PK_{tableName}\" PRIMARY KEY (");
                        script.Append(string.Join(", ", primaryKeyColumns.Select(c => $"\"{c.ColumnName}\"")));
                        script.AppendLine(")");
                    }

                    script.AppendLine(");");
                }
                else
                {
                    // 其他数据库使用原来的语法
                    script.AppendLine($"CREATE TABLE {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}] (");

                    for (int i = 0; i < tableInfo.Columns.Count; i++)
                    {
                        var column = tableInfo.Columns[i];
                        script.Append($"    [{column.ColumnName}] {column.DataType}");

                        if (column.MaxLength > 0 && !column.DataType.Equals("text", StringComparison.OrdinalIgnoreCase))
                        {
                            script.Append($"({column.MaxLength})");
                        }

                        if (column.IsIdentity)
                        {
                            script.Append(" IDENTITY(1,1)");
                        }

                        if (!column.IsNullable)
                        {
                            script.Append(" NOT NULL");
                        }

                        if (!string.IsNullOrEmpty(column.DefaultValue))
                        {
                            script.Append($" DEFAULT {column.DefaultValue}");
                        }

                        if (i < tableInfo.Columns.Count - 1)
                        {
                            script.AppendLine(",");
                        }
                        else
                        {
                            script.AppendLine();
                        }
                    }

                    // Add primary key constraint
                    var primaryKeyColumns = tableInfo.Columns.Where(c => c.IsPrimaryKey).ToList();
                    if (primaryKeyColumns.Any())
                    {
                        script.Append($"    CONSTRAINT [PK_{tableName}] PRIMARY KEY (");
                        script.Append(string.Join(", ", primaryKeyColumns.Select(c => $"[{c.ColumnName}]")));
                        script.AppendLine(")");
                    }

                    script.AppendLine(");");
                }

                return script.ToString();
            }
            catch (Exception ex)
            {
                return $"-- Error generating create table script: {ex.Message}";
            }
        }

        public async Task<string> GenerateInsertScriptAsync(DatabaseConnection connection, string tableName, string? schema = null)
        {
            try
            {
                var tableInfo = await GetTableInfoAsync(connection, tableName, schema);
                var script = new System.Text.StringBuilder();

                if (connection.Type == DatabaseType.PostgreSQL)
                {
                    script.Append($"INSERT INTO {(string.IsNullOrEmpty(schema) ? "" : $"\"{schema}\".")}\"{tableName}\" (");
                    script.Append(string.Join(", ", tableInfo.Columns
                        .Where(c => !c.IsIdentity)
                        .Select(c => $"\"{c.ColumnName}\"")));
                    script.AppendLine(")");

                    script.Append("VALUES (");
                    script.Append(string.Join(", ", tableInfo.Columns
                        .Where(c => !c.IsIdentity)
                        .Select(c => $"${tableInfo.Columns.IndexOf(c) + 1}")));
                    script.AppendLine(");");
                }
                else
                {
                    script.Append($"INSERT INTO {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}] (");
                    script.Append(string.Join(", ", tableInfo.Columns
                        .Where(c => !c.IsIdentity)
                        .Select(c => $"[{c.ColumnName}]")));
                    script.AppendLine(")");

                    script.Append("VALUES (");
                    script.Append(string.Join(", ", tableInfo.Columns
                        .Where(c => !c.IsIdentity)
                        .Select(c => $"@{c.ColumnName}")));
                    script.AppendLine(");");
                }

                return script.ToString();
            }
            catch (Exception ex)
            {
                return $"-- Error generating insert script: {ex.Message}";
            }
        }

        public async Task<string> GenerateUpdateScriptAsync(DatabaseConnection connection, string tableName, string? schema = null)
        {
            try
            {
                var tableInfo = await GetTableInfoAsync(connection, tableName, schema);
                var script = new System.Text.StringBuilder();

                if (connection.Type == DatabaseType.PostgreSQL)
                {
                    script.AppendLine($"UPDATE {(string.IsNullOrEmpty(schema) ? "" : $"\"{schema}\".")}\"{tableName}\"");
                    script.AppendLine("SET");

                    var nonPrimaryKeyColumns = tableInfo.Columns.Where(c => !c.IsPrimaryKey && !c.IsIdentity).ToList();
                    for (int i = 0; i < nonPrimaryKeyColumns.Count; i++)
                    {
                        var column = nonPrimaryKeyColumns[i];
                        script.Append($"    \"{column.ColumnName}\" = ${tableInfo.Columns.IndexOf(column) + 1}");

                        if (i < nonPrimaryKeyColumns.Count - 1)
                        {
                            script.AppendLine(",");
                        }
                        else
                        {
                            script.AppendLine();
                        }
                    }

                    var primaryKeyColumns = tableInfo.Columns.Where(c => c.IsPrimaryKey).ToList();
                    if (primaryKeyColumns.Any())
                    {
                        script.AppendLine("WHERE");

                        for (int i = 0; i < primaryKeyColumns.Count; i++)
                        {
                            var column = primaryKeyColumns[i];
                            script.Append($"    \"{column.ColumnName}\" = ${tableInfo.Columns.IndexOf(column) + 1}");

                            if (i < primaryKeyColumns.Count - 1)
                            {
                                script.AppendLine(" AND");
                            }
                            else
                            {
                                script.AppendLine(";");
                            }
                        }
                    }
                }
                else
                {
                    script.AppendLine($"UPDATE {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}]");
                    script.AppendLine("SET");

                    var nonPrimaryKeyColumns = tableInfo.Columns.Where(c => !c.IsPrimaryKey && !c.IsIdentity).ToList();
                    for (int i = 0; i < nonPrimaryKeyColumns.Count; i++)
                    {
                        var column = nonPrimaryKeyColumns[i];
                        script.Append($"    [{column.ColumnName}] = @{column.ColumnName}");

                        if (i < nonPrimaryKeyColumns.Count - 1)
                        {
                            script.AppendLine(",");
                        }
                        else
                        {
                            script.AppendLine();
                        }
                    }

                    var primaryKeyColumns = tableInfo.Columns.Where(c => c.IsPrimaryKey).ToList();
                    if (primaryKeyColumns.Any())
                    {
                        script.AppendLine("WHERE");

                        for (int i = 0; i < primaryKeyColumns.Count; i++)
                        {
                            var column = primaryKeyColumns[i];
                            script.Append($"    [{column.ColumnName}] = @{column.ColumnName}");

                            if (i < primaryKeyColumns.Count - 1)
                            {
                                script.AppendLine(" AND");
                            }
                            else
                            {
                                script.AppendLine(";");
                            }
                        }
                    }
                }

                return script.ToString();
            }
            catch (Exception ex)
            {
                return $"-- Error generating update script: {ex.Message}";
            }
        }

        public async Task<string> GenerateDeleteScriptAsync(DatabaseConnection connection, string tableName, string? schema = null)
        {
            try
            {
                var tableInfo = await GetTableInfoAsync(connection, tableName, schema);
                var script = new System.Text.StringBuilder();

                if (connection.Type == DatabaseType.PostgreSQL)
                {
                    script.AppendLine($"DELETE FROM {(string.IsNullOrEmpty(schema) ? "" : $"\"{schema}\".")}\"{tableName}\"");

                    var primaryKeyColumns = tableInfo.Columns.Where(c => c.IsPrimaryKey).ToList();
                    if (primaryKeyColumns.Any())
                    {
                        script.AppendLine("WHERE");

                        for (int i = 0; i < primaryKeyColumns.Count; i++)
                        {
                            var column = primaryKeyColumns[i];
                            script.Append($"    \"{column.ColumnName}\" = ${tableInfo.Columns.IndexOf(column) + 1}");

                            if (i < primaryKeyColumns.Count - 1)
                            {
                                script.AppendLine(" AND");
                            }
                            else
                            {
                                script.AppendLine(";");
                            }
                        }
                    }
                }
                else
                {
                    script.AppendLine($"DELETE FROM {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}]");

                    var primaryKeyColumns = tableInfo.Columns.Where(c => c.IsPrimaryKey).ToList();
                    if (primaryKeyColumns.Any())
                    {
                        script.AppendLine("WHERE");

                        for (int i = 0; i < primaryKeyColumns.Count; i++)
                        {
                            var column = primaryKeyColumns[i];
                            script.Append($"    [{column.ColumnName}] = @{column.ColumnName}");

                            if (i < primaryKeyColumns.Count - 1)
                            {
                                script.AppendLine(" AND");
                            }
                            else
                            {
                                script.AppendLine(";");
                            }
                        }
                    }
                }

                return script.ToString();
            }
            catch (Exception ex)
            {
                return $"-- Error generating delete script: {ex.Message}";
            }
        }

        public async Task<string> GenerateSelectScriptAsync(DatabaseConnection connection, string tableName, string? schema = null)
        {
            try
            {
                var tableInfo = await GetTableInfoAsync(connection, tableName, schema);
                var script = new System.Text.StringBuilder();

                if (connection.Type == DatabaseType.PostgreSQL)
                {
                    script.Append($"SELECT ");
                    script.Append(string.Join(", ", tableInfo.Columns.Select(c => $"\"{c.ColumnName}\"")));
                    script.AppendLine($" FROM {(string.IsNullOrEmpty(schema) ? "" : $"\"{schema}\".")}\"{tableName}\";");
                }
                else
                {
                    script.Append($"SELECT ");
                    script.Append(string.Join(", ", tableInfo.Columns.Select(c => $"[{c.ColumnName}]")));
                    script.AppendLine($" FROM {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}];");
                }

                return script.ToString();
            }
            catch (Exception ex)
            {
                return $"-- Error generating select script: {ex.Message}";
            }
        }
        public async Task<QueryResult> ExecuteQueryWithPagingAsync(DatabaseConnection connection, string tableName, string? schema = null, int page = 1, int pageSize = 20)
        {
            if (page < 1) page = 1;
            if (pageSize < 1) pageSize = 20;

            var result = new QueryResult
            {
                IsSuccess = false,
                CurrentPage = page,
                PageSize = pageSize
            };

            var dbHelper = GetDbHelper(connection);
            var stopwatch = new Stopwatch();

            try
            {
                // 首先获取总记录数
                string countQuery = string.Empty;
                // 然后获取分页数据
                string dataQuery = string.Empty;

                switch (connection.Type)
                {
                    case DatabaseType.SqlServer:
                        countQuery = $"SELECT COUNT(*) FROM {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}]";
                        dataQuery = $@"
                            SELECT * FROM (
                                SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum, *
                                FROM {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}]
                            ) AS RowConstrainedResult
                            WHERE RowNum > {(page - 1) * pageSize} AND RowNum <= {page * pageSize}
                            ORDER BY RowNum";
                        break;
                    case DatabaseType.MySQL:
                        countQuery = $"SELECT COUNT(*) FROM {(string.IsNullOrEmpty(schema) ? "" : $"`{schema}`.")}`{tableName}`";
                        dataQuery = $@"SELECT * FROM {(string.IsNullOrEmpty(schema) ? "" : $"`{schema}`.")}`{tableName}` LIMIT {(page - 1) * pageSize}, {pageSize}";
                        break;
                    case DatabaseType.Oracle:
                        countQuery = $"SELECT COUNT(*) FROM {(string.IsNullOrEmpty(schema) ? "" : $"\"{schema}\".")}\"{tableName}\"";
                        dataQuery = $@"
                            SELECT * FROM (
                                SELECT a.*, ROWNUM rnum FROM (
                                    SELECT * FROM {(string.IsNullOrEmpty(schema) ? "" : $"\"{schema}\".")}""{tableName}""
                                ) a WHERE ROWNUM <= {page * pageSize}
                            ) WHERE rnum > {(page - 1) * pageSize}";
                        break;
                    case DatabaseType.SQLite:
                        countQuery = $"SELECT COUNT(*) FROM {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}]";
                        dataQuery = $@"SELECT * FROM {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}] LIMIT {pageSize} OFFSET {(page - 1) * pageSize}";
                        break;
                    case DatabaseType.PostgreSQL:
                        countQuery = $"SELECT COUNT(*) FROM {(string.IsNullOrEmpty(schema) ? "" : $"\"{schema}\".")}\"{tableName}\"";
                        dataQuery = $@"SELECT * FROM {(string.IsNullOrEmpty(schema) ? "" : $"\"{schema}\".")}""{tableName}"" LIMIT {pageSize} OFFSET {(page - 1) * pageSize}";
                        break;
                    case DatabaseType.Access:
                        // Access不支持OFFSET FETCH，尝试使用TOP
                        countQuery = $"SELECT COUNT(*) FROM {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}]";
                        if (page == 1)
                        {
                            dataQuery = $@"SELECT TOP {pageSize} * FROM {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}]";
                        }
                        else
                        {
                            // 这种方式适用于有主键的表
                            // 实际应用中可能需要更复杂的实现
                            dataQuery = $@"
                                SELECT TOP {pageSize} * FROM {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}]
                                WHERE (SELECT COUNT(*) FROM {(string.IsNullOrEmpty(schema) ? "" : $"[{schema}].")}[{tableName}]) > {(page - 1) * pageSize}";
                        }
                        break;
                    default:
                        result.ErrorMessage = "不支持的数据库类型";
                        return result;
                }

                stopwatch.Start();

                // 执行计数查询
                var countResult = await Task.Run(() => dbHelper.ExecuteScalar(countQuery));
                result.TotalRecords = Convert.ToInt32(countResult);

                // 执行数据查询
                var dt = await Task.Run(() => dbHelper.ExecuteDataSet(dataQuery));

                stopwatch.Stop();

                if (dt != null && dt.Tables.Count > 0)
                {
                    result.ResultSets.Add(dt.Tables[0]);
                }
                result.ExecutionTime = stopwatch.Elapsed;
                result.IsSuccess = true;
                result.Query = dataQuery;

                return result;
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                result.ErrorMessage = ex.Message;
                result.ExecutionTime = stopwatch.Elapsed;
                return result;
            }
        }
    }
}